﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml.Linq;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
namespace University.Workshop.DataAccess
{
    public static class DataAccessGifts
    {
    public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=Pharmacy;Integrated Security=true";
        
        public static void AddDataGift(string nameGift, string priceGift)
        {
            string sqlSentence = "INSERT INTO [Premios](Nombre,Precio,Habilitado,FechaDeIngreso) VALUES(@name,@price,1,@date)";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.Parameters.AddWithValue("@name", nameGift);
                cmd.Parameters.AddWithValue("@price", priceGift);
                cmd.Parameters.AddWithValue("@date", System.DateTime.Now);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
        public static DataTable GetGiftByName(string name)
        {
            string sqlSentence = "SELECT * FROM [Premios] Where Nombre = @name AND Habilitado = 1";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.Parameters.AddWithValue("@name", name);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);

            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
            return table;
        }
        public static bool DeleteGiftByName(string name)
        {
            string sqlSentence = "UPDATE [Premios]  SET [Habilitado] = 0 WHERE [Nombre] = @name ";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            try
            {
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.Parameters.AddWithValue("@name", name);
                int result = cmd.ExecuteNonQuery();
                return result != 0;

            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }

        }

        public static bool DeleteDataGiftByName(string name)
        {
            string sqlSentence = "Delete From [Premios]  WHERE [Nombre]=@name ";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;

            try
            {
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.Parameters.AddWithValue("@name", name);
                int result = cmd.ExecuteNonQuery();
                return result != 0;

            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }

        }
        public static DataColumn GetGiftByPrices(double price1, double price2)
        {
            string sqlSentence = "SELECT Nombre FROM [Premios] Where Precio <= @price2 AND Precio > @price1 AND Habilitado = 1";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.Parameters.AddWithValue("@price1", price1);
                cmd.Parameters.AddWithValue("@price2", price2);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);

            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
            return table.Columns[0];
        }
       
        public static DataTable GetAllGifts(string gifts)
        {
            string sqlSentence;
            if (gifts == "todos")
            {
                sqlSentence = "SELECT * FROM [Premios]";
            }
            else {
                sqlSentence = "SELECT Nombre,Precio FROM [Premios] Where Habilitado=1";
            }
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);

            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
            return table;
        }
   
    }
}
